-- @owner: @haomeng802
-- @date: 2023/1/4
-- @testpoint: connect by子句,结合alias/with子查询,部分测试点合理报错


--step1:创建普通表，插入数据;expect:建表成功，插入数据成功
drop table if exists t_connectby_019 cascade;
create table t_connectby_019(name varchar, id int, fatherid int);
insert into t_connectby_019 values('A', 1, 0), ('B', 2, 1),('C',3,1),('D',4,1),('E',5,2),('A',0,1),('A',1,2);

--step2:结合alias子句，查询id>fatherid的递归查询;expect:成功
select alias1.name as alias2 from t_connectby_019 as alias1 start with alias1.id connect by nocycle prior alias1.id >= alias1.fatherid limit 10;

--step3:结合with子句，进行递归查询;expect:因递归查询次数>迭代次数，合理报错
with alias5 as (select alias1.name as alias2 from t_connectby_019 as alias1 connect by nocycle alias1.id >= alias1.fatherid),
alias8 as (select * from alias5 connect by nocycle prior alias5.alias2 != alias5.alias2)
select * from alias8,t_connectby_019 connect by nocycle prior t_connectby_019.id < t_connectby_019.fatherid;

--step4:清理环境;expect:成功
drop table t_connectby_019 cascade;
